package com.admin.dao;

import java.sql.SQLException;

import net.sf.ehcache.Cache;
import net.sf.ehcache.Element;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.admin.cache.EhCacheManager;
import com.admin.service.ProvinceService;

@Repository
public class ProvinceDao {

	private static final Logger log = Logger.getLogger(ProvinceDao.class);
	
	@Autowired private QueryRunner queryRunner;
	
	public int getProvinceId(String telPhone) throws SQLException{
		if(telPhone == null || telPhone.length()<7) {
			return 0;
		}
		String phoneSub = telPhone.substring(0,7);
		String sql = "select * from region_infos where phnId = ?";
		Object provinceName =  queryRunner.query(sql, new ScalarHandler("province"), phoneSub);
		if(provinceName==null){
			return 0;
		}else{
			return ProvinceService.getProvince(provinceName.toString()).getId();
		}
	}
	
	/**
	 * 根据smsp取区号
	 * @param smsp
	 * @return
	 * @throws SQLException
	 */
	public String getAreaId(String smsp) throws SQLException{
		if(smsp == null || smsp.length()<11) {
			return "0";
		}
		String sql = "select * from res_smsp where smsp=?";
		String areaId = (String)queryRunner.query(sql, new ScalarHandler("regionArea"), smsp);
		return areaId;
	}
	
	/**
	 * 根据区号取省份ID
	 * @param areaId
	 * @return
	 * @throws SQLException
	 */
	public int getProvinceIdByAreaId(String areaId) throws SQLException{
		log.info("区号："+areaId);
		if(areaId==null||areaId.equals("")||areaId.equals("0")){
			return 0;
		}
		String sql = "select province from region_infos where region = ?";
		String provinceName = (String) queryRunner.query(sql, new ScalarHandler("province"), Integer.valueOf(areaId));
		log.info("省份名:"+provinceName);
		if(provinceName==null || provinceName.length()==0){
			return 0;
		}
		return ProvinceService.getProvince(provinceName).getId();
	}
	
	/**
	 * 根据SMSP获取省份ID
	 * @param smsp
	 * @return
	 */
	public int getProvinceIdBySmsp(String smsp){
		if(smsp==null || "".equals(smsp) || smsp.startsWith("R")){
			return 0;
		}
		try {
			String areaId = null;
			Cache cache = EhCacheManager.getCache();
			switch (smsp.length()) {
				case 10:
					smsp = smsp+"0";
					break;
				case 9:
					smsp = smsp+"00";
					break;
				case 8:
					smsp = smsp+"500";
					break;
				default:
					break;
			}
			Element areaElement = cache.get(EhCacheManager.PROVINCE_AREA+smsp);
			if(areaElement==null){
				areaId = getAreaId(smsp);
				cache.put(new Element(EhCacheManager.PROVINCE_AREA+smsp, areaId));
			}else{
				areaId = (String)areaElement.getValue();
			}
			Element provinceElement = cache.get(EhCacheManager.PROVINCE_ID+areaId);
			int provinceId = 0;
			if(provinceElement==null){
				provinceId = getProvinceIdByAreaId(areaId);
				cache.put(new Element(EhCacheManager.PROVINCE_ID+areaId, provinceId));
			}else{
				provinceId = (Integer) provinceElement.getValue();
			}
			return provinceId;
		} catch (SQLException e) {
			e.printStackTrace();
			return 0;
		}
		
	}
}
